﻿using QuanLyGiaoVuDAL.ADOWrapper;
using QuanLyGiaoVuDAL.Mapper;
using QuanLyGiaoVuDAL.DTO;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;


namespace QuanLyGiaoVuDAL.Repositories.DatabaseRepositories
{
    public class GiangVienRepository : IGiangVienRepository
    {
        public GiangVienDTO GetGiangVienById(Guid id)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_GIANGVIENSelect]", new SqlParameter("@ID", id));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToGiangVienDTO();
        }

        public IEnumerable<DTO.GiangVienDTO> GetAllGiangVien()
        {
            List<GiangVienDTO> giangvienList = new List<GiangVienDTO>();
            var parameter = DBParameterFactory.Create("ID", SqlDbType.UniqueIdentifier, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_GIANGVIENSelect]", parameter);
            var giangvienTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in giangvienTable)
            {
                giangvienList.Add(dataRow.ToGiangVienDTO());
            }
            return giangvienList.AsEnumerable();
        }

        public DTO.GiangVienDTO InsertGiangVien(DTO.GiangVienDTO giangVien)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_GIANGVIENInsert]", GetParameterArray(giangVien));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToGiangVienDTO();
        }

        public DTO.GiangVienDTO UpdateGiangVien(DTO.GiangVienDTO giangVien)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_GIANGVIENUpdate]", GetParameterArray(giangVien));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToGiangVienDTO();
        }

        public bool DeleteGiangVien(String giangVienIds)
        {
            return StoreProcedureExecutor.ExecuteNonQueryStoredProcedure("[dbo].[usp_GIANGVIENDelete]", new SqlParameter("@ID", giangVienIds));
        }


        public HocViDTO GetHocViById(int maHocVi)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_HOCVISelect]", new SqlParameter("@MaHocVi", maHocVi));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToHocViDTO();
        }

        public IEnumerable<HocViDTO> GetAllHocVi()
        {
            List<HocViDTO> hocViList = new List<HocViDTO>();
            SqlParameter parameter = new SqlParameter("@MaHocVi", SqlDbType.Int);
            parameter.Value = DBNull.Value;
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_HOCVISelect]", parameter);
            var hocviTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in hocviTable)
            {
                hocViList.Add(dataRow.ToHocViDTO());
            }
            return hocViList.AsEnumerable();
        }

        public HocHamDTO GetHocHamById(int maHocHam)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_HOCVISelect]", new SqlParameter("@MaHocHam", maHocHam));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToHocHamDTO();
        }

        public IEnumerable<HocHamDTO> GetAllHocHam()
        {
            List<HocHamDTO> hocHamList = new List<HocHamDTO>();
            SqlParameter parameter = new SqlParameter("@MaHocHam", SqlDbType.Int);
            parameter.Value = DBNull.Value;
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_HOCHAMSelect]", parameter);
            var hocHamTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in hocHamTable)
            {
                hocHamList.Add(dataRow.ToHocHamDTO());
            }
            return hocHamList.AsEnumerable();
        }

        public ChucVuDTO GetChucVuByMaChucVu(Guid maChucVu)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_CHUCVUSelect]", new SqlParameter("@MaChucVu", maChucVu));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToChucVuDTO();
        }

        public IEnumerable<ChucVuDTO> GetAllChucVu()
        {
            List<ChucVuDTO> ChucVuList = new List<ChucVuDTO>();
            SqlParameter parameter = new SqlParameter("@MaChucVu", SqlDbType.UniqueIdentifier);
            parameter.Value = DBNull.Value;
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_ChucVuSelect]", parameter);
            var ChucVuTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in ChucVuTable)
            {
                ChucVuList.Add(dataRow.ToChucVuDTO());
            }
            return ChucVuList.AsEnumerable();
        }
        public IEnumerable<ChucVuDTO> GetAllChucVuGiangVien(Guid giangVienId)
        {
            List<ChucVuDTO> ChucVuList = new List<ChucVuDTO>();
            SqlParameter giangVienIdParam = DBParameterFactory.Create("MaGiangVien", giangVienId);
            SqlParameter chucVuIdParam = DBParameterFactory.Create("MaChucVu", SqlDbType.UniqueIdentifier, DBNull.Value);
            chucVuIdParam.Value = DBNull.Value;
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_GiangVienChucVuSelect]", giangVienIdParam, chucVuIdParam);
            
            var chucVuTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in chucVuTable)
            {
                ChucVuList.Add(dataRow.ToGiangVienChucVuDTO());
            }
            return ChucVuList.AsEnumerable();
        }

        public IEnumerable<MonHocDTO> GetAllMonHocByGiangVienId(Guid giangVienId)
        {
            SqlParameter giangVienIdParam = new SqlParameter("@giangVienId", giangVienId);
            SqlParameter monHocIdParam = new SqlParameter("@MonHocId", DbType.Guid);
            monHocIdParam.Value = DBNull.Value;
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_KHANANGSelect]", giangVienIdParam, monHocIdParam);
            var tableResult = dbResult.Tables[0].Rows;

            List<MonHocDTO> danhSachMonHoc = new List<MonHocDTO>();

            foreach (DataRow dataRow in tableResult)
            {
                danhSachMonHoc.Add(dataRow.ParseKhaNangToMonHocDTO());
            }
            return danhSachMonHoc.AsEnumerable();
        }

        public bool InsertMonHoc(Guid giangVienId, Guid monHocId)
        {
            SqlParameter giangVienIdParam = new SqlParameter("@giangVienId", giangVienId);
            SqlParameter monHocIdParam = new SqlParameter("@MonHocId", monHocId);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_KHANANGInsert]", giangVienIdParam, monHocIdParam);
            var dataRows = dbResult.Tables[0].Rows;
            return (dataRows.Count > 0);
        }

        public bool DeleteMonHoc(Guid giangVienId, Guid monHocId)
        {
            SqlParameter giangVienIdParam = new SqlParameter("@giangVienId", giangVienId);
            SqlParameter monHocIdParam = new SqlParameter("@MonHocId", monHocId);
            return StoreProcedureExecutor.ExecuteNonQueryStoredProcedure("[dbo].[usp_KHANANGDelete]", giangVienIdParam, monHocIdParam);
        }

        public bool InsertChucVu(Guid giangVienId, Guid chucVuId)
        {
            SqlParameter giangVienIdParam = new SqlParameter("@giangVienId", giangVienId);
            SqlParameter chucVuIdParam = new SqlParameter("@ChucVuId", chucVuId);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_CHITIETCHUCVUInsert]", giangVienIdParam, chucVuIdParam);
            var dataRows = dbResult.Tables[0].Rows;
            return (dataRows.Count > 0);
        }

        public bool DeleteChucVu(Guid giangVienId, Guid chucVuId)
        {
            SqlParameter giangVienIdParam = new SqlParameter("@giangVienId", giangVienId);
            SqlParameter chucVuIdParam = new SqlParameter("@ChucVuId", chucVuId);
            return StoreProcedureExecutor.ExecuteNonQueryStoredProcedure("[dbo].[usp_CHITIETCHUCVUDelete]", giangVienIdParam, chucVuIdParam);
        }

        #region Private methods
        private SqlParameter[] GetParameterArray(GiangVienDTO giangVien)
        {
            SqlParameter[] parameters = new SqlParameter[15];
            parameters[0] = new SqlParameter("@ID", giangVien.ID);
            parameters[1] = new SqlParameter("@MaGiangVien", giangVien.MaGiangVien);
            parameters[2] = new SqlParameter("@HotenGV", giangVien.HoTenGV);
            parameters[3] = new SqlParameter("@GioiTinh", giangVien.GioiTinh == 1);
            parameters[4] = giangVien.NgaySinh.HasValue ? DBParameterFactory.Create("NgaySinh", giangVien.NgaySinh) : DBParameterFactory.Create("NgaySinh", SqlDbType.DateTime, DBNull.Value);
            parameters[5] = new SqlParameter("@NgayVaoCongTac", giangVien.NgayVaoCongTac);
            parameters[6] = new SqlParameter("@HocVi", giangVien.MaHocVi);
            parameters[7] = new SqlParameter("@HocHam", giangVien.MaHocHam);
            if (giangVien.MaHocHam == null)
                parameters[7] = DBParameterFactory.Create("HocHam", SqlDbType.Int, DBNull.Value);
            parameters[8] = new SqlParameter("@MaChucDanh", giangVien.MaChucDanh);
            if (giangVien.MaChucDanh == null || giangVien.MaChucDanh.Equals(Guid.Empty))
                parameters[8].Value = DBNull.Value;
            parameters[9] = new SqlParameter("@DiaChi", giangVien.DiaChi);
            if (giangVien.DiaChi == null)
            {
                parameters[9].DbType = DbType.String;
                parameters[9].Value = DBNull.Value;
            }

            parameters[10] = new SqlParameter("@SoDienThoai", giangVien.SoDienThoai);
            if (giangVien.SoDienThoai == null)
            {
                parameters[10].DbType = DbType.String;
                parameters[10].Value = DBNull.Value;
            }
            parameters[11] = new SqlParameter("@Email", giangVien.Email);
            if (giangVien.Email == null)
            {
                parameters[11].DbType = DbType.String;
                parameters[11].Value = DBNull.Value;
            }
            parameters[12] = DBParameterFactory.Create("ThinhGiang", giangVien.ThinhGiang);
            parameters[13] = giangVien.MaBoMon.HasValue ? DBParameterFactory.Create("MaBoMon", giangVien.MaBoMon) : DBParameterFactory.Create("MaBoMon", SqlDbType.Int, DBNull.Value);
            parameters[14] = DBParameterFactory.Create("DaXoa", giangVien.DaXoa);

            return parameters;
        }

        private SqlParameter[] GetParameterArray(LichRanhDTO lichRanhDTO)
        {
            SqlParameter[] parameters = new SqlParameter[4];
            parameters[0] = DBParameterFactory.Create("MaLichRanh", lichRanhDTO.MaLichRanh);
            parameters[1] = DBParameterFactory.Create("GiangVienId", lichRanhDTO.GiangVien);
            parameters[2] = DBParameterFactory.Create("Buoi", lichRanhDTO.Buoi);
            parameters[3] = DBParameterFactory.Create("Thu", lichRanhDTO.Thu);
            return parameters;
        }
        #endregion

        public IEnumerable<ChucDanhDTO> GetAllChucDanh()
        {
            List<ChucDanhDTO> chucDanhList = new List<ChucDanhDTO>();
            var parameter = DBParameterFactory.Create("MaChucDanh", SqlDbType.UniqueIdentifier, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_CHUCDANHSelect]", parameter);
            var giangvienTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in giangvienTable)
            {
                chucDanhList.Add(dataRow.ToChucDanhDTO());
            }
            return chucDanhList.AsEnumerable();
        }

        public ChucDanhDTO GetChucDanhByMaChucDanh(Guid maChucDanh)
        {
            var parameter = DBParameterFactory.Create("MaChucDanh", SqlDbType.UniqueIdentifier, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_CHUCDANHSelect]", parameter);
            var chucDanhRow = dbResult.Tables[0].Rows[0];
            return chucDanhRow.ToChucDanhDTO();
        }


        public LichRanhDTO GetLichRanhById(Guid lichRanhId)
        {
            var paramMaLichThi = DBParameterFactory.Create("MaLichRanh", lichRanhId);
            var paramMaGiangVien = DBParameterFactory.Create("MaGiangVien", SqlDbType.UniqueIdentifier, DBNull.Value);
            var paramBuoi = DBParameterFactory.Create("Buoi", SqlDbType.Bit, DBNull.Value);
            var paramThu = DBParameterFactory.Create("Thu", SqlDbType.Int, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_LICHRANHSelect]", paramMaLichThi, paramMaGiangVien, paramBuoi, paramThu);
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToLichRanhDTO();
        }

        public IEnumerable<LichRanhDTO> GetAllLichRanh(Guid? maGiangVien, bool? buoi, int? thu)
        {
            List<LichRanhDTO> danhSachLichRanh = new List<LichRanhDTO>();
            var paramMaLichThi = DBParameterFactory.Create("MaLichRanh", SqlDbType.UniqueIdentifier, DBNull.Value);
            var paramMaGiangVien = maGiangVien != null ? DBParameterFactory.Create("MaGiangVien", SqlDbType.UniqueIdentifier, maGiangVien) : DBParameterFactory.Create("MaGiangVien", SqlDbType.UniqueIdentifier, DBNull.Value);
            var paramBuoi = buoi != null ?  DBParameterFactory.Create("Buoi", buoi) : DBParameterFactory.Create("Buoi", SqlDbType.Bit, DBNull.Value);
            var paramThu = thu != null ? DBParameterFactory.Create("Thu", SqlDbType.Int, thu) : DBParameterFactory.Create("Thu", SqlDbType.Int, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_LICHRANHSelect]", paramMaLichThi, paramMaGiangVien, paramBuoi, paramThu);
            var dataTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in dataTable)
            {
                danhSachLichRanh.Add(dataRow.ToLichRanhDTO());
            }
            return danhSachLichRanh;
        }

        public LichRanhDTO InsertLichRanh(LichRanhDTO lichRanhDTO)
        {
            try
            {
                var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_LICHRANHInsert]", GetParameterArray(lichRanhDTO));
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToLichRanhDTO();
            }
            catch (Exception exception)
            {
                throw new Exception("Không thể thêm dữ liệu lịch rãnh giảng viên.");
            }
        }

        public bool DeleteLichRanh(Guid maLichRanh)
        {
            SqlParameter maLichRanhParam = DBParameterFactory.Create("MaLichRanh", maLichRanh);
            return StoreProcedureExecutor.ExecuteNonQueryStoredProcedure("[dbo].[usp_LICHRANHDelete]", maLichRanhParam);
        }


        public GiangVienDTO GetGiangVienByEmail(string email)
        {
            try
            {
                var idParameter = DBParameterFactory.Create("ID", SqlDbType.UniqueIdentifier, DBNull.Value);
                var emailParameter = DBParameterFactory.Create("Email", SqlDbType.VarChar, email);
                var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_GIANGVIENSelect]", idParameter, emailParameter);
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToGiangVienDTO();
            }
            catch
            {
                return null;
            }
        }


        public IEnumerable<BoMonDTO> GetAllBoMon()
        {
            List<BoMonDTO> BoMonList = new List<BoMonDTO>();
            SqlParameter parameter = DBParameterFactory.Create("MaBoMon", SqlDbType.Int, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_BOMONSelect]", parameter);
            var BoMonTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in BoMonTable)
            {
                BoMonList.Add(dataRow.ToBoMonDTO());
            }
            return BoMonList.AsEnumerable();
        }
    }
}
